package com.softer.chengxin.excel.dao.provider;

import com.softer.chengxin.common.enums.StatisticalReportEnum;
import com.softer.chengxin.excel.domin.ExportExcel;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author Gong Zhiyang
 * @date 2019/2/20
 */
public class StatisticalReportDaoProvider {
    public static final String C = "c";

    /**
     * 查询企业数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getCorpNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_base_info c WHERE c.state in(2,9)");
        commonSql(sql, map, C, C);
        return sql.toString();
    }

    /**
     * 查询项目数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getProjectNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_project c WHERE c.state in (2,9)");
        commonSql(sql, map, C, C);
        return sql.toString();
    }

    /**
     * 查询三体系认证数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getThreeSystemNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_base_info c WHERE c.state in (2,9) and c.three_system=#{state}");
        commonSql(sql, map, C, C);
        return sql.toString();
    }

    /**
     * 查询党支部数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getOrganizationNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_base_info c WHERE c.state in(2,9) and c.organization=#{state}");
        commonSql(sql, map, C, C);
        return sql.toString();
    }

    /**
     * 查询主营收入
     *
     * @param map 参数
     * @return SQL
     */
    public String getIncome(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        String type = (String) map.get("type");
        sql.append("SELECT IFNULL(SUM(").append(type).append("),0) FROM company_base_info c WHERE c.state in(2,9) ");
        commonSql(sql, map, C, C);
        return sql.toString();
    }

    /**
     * 查询企业人数
     *
     * @param map 参数
     * @return SQL
     */
    public String getCorpPersonNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_person p LEFT JOIN company_base_info c ON p.company_id=c.id WHERE p.person_id=0 and c.state in(2,9) ");
        String parameter = (String) map.get("parameter");
        if (StringUtils.isNotBlank(parameter)) {
            if (StatisticalReportEnum.CORP_INSURANCE.getField().equals(parameter)) {
                sql.append(" and p.insurance=1");
            } else if (StatisticalReportEnum.CORP_CERTIFICATE.getField().equals(parameter)) {
                sql.append(" and p.certificate=1");
            }
        }
        commonSql(sql, map, C, "p");
        return sql.toString();
    }

    /**
     * 查询项目人数
     *
     * @param map 参数
     * @return SQL
     */
    public String getProjectPersonNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_person p LEFT JOIN company_project c ON p.project_id=c.id WHERE p.person_id<>0 and c.state=2 ");
        String parameter = (String) map.get("parameter");
        if (StringUtils.isNotBlank(parameter)) {
            if (StatisticalReportEnum.PROJECT_INSURANCE.getField().equals(parameter)) {
                sql.append(" and p.insurance=1");
            } else if (StatisticalReportEnum.PROJECT_CERTIFICATE.getField().equals(parameter)) {
                sql.append(" and p.certificate=1");
            }
        }
        commonSql(sql, map, C, "p");
        return sql.toString();
    }

    /**
     * 查询各个等级的数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getLevelNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(poguid) FROM score_record s LEFT JOIN company_base_info c on s.poguid=c.id");
        sql.append(" WHERE s.dj=#{level} AND s.`year`=#{year} and c.state in(2,9) ");
        commonSql(sql, map, C, "s");
        return sql.toString();
    }

    /**
     * 查询黑名单数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getBlackList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_blacklist b LEFT JOIN company_base_info c on b.company_id = c.id WHERE b.state=1");
        commonSql(sql, map, C, "b");
        return sql.toString();
    }

    /**
     * 查询项目各种面积
     *
     * @param map 参数
     * @return SQL
     */
    public String getProjectArea(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        String type = (String) map.get("type");
        sql.append("SELECT IFNULL(SUM(").append(type).append("),0) FROM company_project c WHERE c.state=2 ");
        commonSql(sql, map, C, C);
        return sql.toString();
    }

    /**
     * 查询老旧小区，智能化管理小区，保障住房数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getHouseNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        String type = (String) map.get("type");
        sql.append("SELECT count(*) FROM company_project c WHERE c.state=2 ");
        if (StringUtils.isNotBlank(type)) {
            if (StatisticalReportEnum.OLD_DISTRICT.getField().equals(type)) {
                sql.append(" and c.is_old=1");
            } else if (StatisticalReportEnum.INTELLIGENT.getField().equals(type)) {
                sql.append(" and c.is_intelligent=1");
            } else if (StatisticalReportEnum.INDEMNIFICATORY.getField().equals(type)) {
                sql.append(" and c.is_indemnificatory=1");
            }
        }
        commonSql(sql, map, C, C);
        return sql.toString();
    }

    /**
     * 查询业委会数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getProprietor(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM company_proprietor p LEFT JOIN company_project c ON p.project_id=c.project_id WHERE c.state=2 ");
        commonSql(sql, map, C, "p");
        return sql.toString();
    }

    /**
     * 查询优良,负面信息数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getInfoNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        sql.append("SELECT COUNT(*) FROM score_detail d ");
        sql.append(" LEFT JOIN company_project p ON d.project_id = p.id");
        sql.append(" LEFT JOIN company_base_info c ON d.corp_id = c.id");
        sql.append(" WHERE d.info_type=#{type} and d.state IN (2, 4, 5, 6, 7)");
        commonInfoSql(sql, map);
        return sql.toString();
    }

    /**
     * 查询教育数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getTrainNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(10);
        String type = (String) map.get("type");
        sql.append("SELECT COUNT(*) FROM company_train t LEFT JOIN company_base_info c ON c.id=t.corp_id");
        sql.append(" WHERE c.state in(2,9)");
        if (StringUtils.isNotBlank(type)) {
            sql.append(" and t.").append(type).append("=#{value}");
        }
        commonSql(sql, map, C, "t");
        return sql.toString();
    }

    public static void commonInfoSql(StringBuffer sql, HashMap<String, Object> map) {
        String area = (String) map.get("area");
        ExportExcel exportExcel = (ExportExcel) map.get("exportExcel");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and IF(d.project_id=0,c.street in (").append(area).append("),");
            sql.append("p.street in (").append(area).append("))");
        }
        if (exportExcel.getDistrict() != null) {
            sql.append(" and IF(d.project_id=0,c.district =#{exportExcel.district},p.district = #{exportExcel.district})");
        }
        if (StringUtils.isNotBlank(exportExcel.getStreet())) {
            sql.append(" and IF(d.project_id=0,c.street=#{exportExcel.street},p.street =#{exportExcel.street})");
        }
        if (StringUtils.isNotBlank(exportExcel.getBeginTime())) {
            sql.append(" and d.gmt_create>=#{exportExcel.beginTime}");
        }
        if (StringUtils.isNotBlank(exportExcel.getEndTime())) {
            sql.append(" and d.gmt_create<=#{exportExcel.endTime}");
        }
    }

    public static void commonSql(StringBuffer sql, HashMap<String, Object> map, String key, String code) {
        String area = (String) map.get("area");
        ExportExcel exportExcel = (ExportExcel) map.get("exportExcel");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and ").append(key).append(".street in (").append(area).append(")");
        }
        if (exportExcel.getDistrict() != null) {
            sql.append(" and ").append(key).append(".district=#{exportExcel.district}");
        }
        if (StringUtils.isNotBlank(exportExcel.getStreet())) {
            sql.append(" and ").append(key).append(".street=#{exportExcel.street}");
        }
        if (StringUtils.isNotBlank(exportExcel.getBeginTime())) {
            sql.append(" and ").append(code).append(".gmt_create>=#{exportExcel.beginTime}");
        }
        if (StringUtils.isNotBlank(exportExcel.getEndTime())) {
            sql.append(" and ").append(code).append(".gmt_create<=#{exportExcel.endTime}");
        }
    }
}
